﻿using System.Collections.Generic;
using System.Data;
using System.Text.RegularExpressions;
using ClosedXML.Excel;

namespace NyanBankIntfTool
{
    class ExcelOperator
    {
        private string excelFileName;
        private XLWorkbook workbook;

        public ExcelOperator()
        {
            workbook = new XLWorkbook();
        }

        public ExcelOperator(string name)
        {
            excelFileName = name;
            workbook = new XLWorkbook(excelFileName);
        }

        public void Save()
        {
            if (!excelFileName.Contains("xlsx"))
            {
                workbook.SaveAs(excelFileName + ".xlsx");
            }
            else
            {
                workbook.SaveAs(excelFileName);
            }
        }

        public void SaveAs(string name)
        {
            if (!name.Contains("xlsx"))
            {
                workbook.SaveAs(name + ".xlsx");
            }
            else
            {
                workbook.SaveAs(name);
            }
        }

        public void AddSheet(string sheetName)
        {
            workbook.Worksheets.Add(sheetName);
        }

        public void AddSheet(DataTable dataTable, string name = null)
        {
            var ws = workbook.Worksheets.Add(dataTable);
            if (name != null)
            {
                ws.Name = name;
            }
        }

        public void InsertData(Dictionary<string, DataSet> data, string flagCol, bool hasTitle = false)
        {
            foreach (var v in workbook.Worksheets)
            {
                for (int i = 100; i >= 1; i--)
                {
                    var cell = v.Cell(i, flagCol);
                    string value = cell.Value as string;
                    if (value == null) continue;


                    Match match;

                    if ((match = Regex.Match(value, @"(.*?)\[(.*?)\]")).Success)
                    {
                        GroupCollection group = match.Groups;
                        string fileName = group[1].Value;
                        string businFlag = group[2].Value;

                        int rowCount = data[fileName].Tables[businFlag].Rows.Count;
                        if (rowCount == 0)
                        {
                            cell.Value = "";
                            //cell = cell.CellAbove();
                            continue;
                        }
                        else if (rowCount > 2)
                        {
                            cell.WorksheetRow().InsertRowsBelow(rowCount - 1);
                        }

                        if (hasTitle)
                        {
                            var rangeWithData = cell.InsertTable(data[fileName].Tables[businFlag]);
                            //cell = cell.CellAbove(rangeWithData.RowCount());
                        }
                        else
                        {
                            var rangeWithData = cell.InsertData(data[fileName].Tables[businFlag]);
                            //cell = cell.CellAbove(rangeWithData.RowCount());
                        }
                    }
                    else
                    {
                        //cell = cell.CellAbove();
                    }

                    //if (cell.Address.RowNumber == 1)
                    //{
                    //    break;
                    //}
                }
            }
        }

        public void InsertData(string fileName, DataSet data)
        {
            foreach (var v in workbook.Worksheets)
            {
                var cell = v.Cell(1, "D");
                for (int i = 1; i <= 100; i++)
                {
                    string value = cell.Value as string;
                    if (value.Contains(fileName))
                    {
                        int begin = value.IndexOf("[");
                        int end = value.IndexOf("]");
                        string businFlag = value.Substring(begin + 1, end - begin - 1);
                        int rowCount = data.Tables[businFlag].Rows.Count;
                        if (rowCount == 0)
                        {
                            cell.Value = "";
                            cell = cell.CellBelow();
                            continue;
                        }
                        else if (rowCount > 2)
                        {
                            cell.WorksheetRow().InsertRowsBelow(rowCount - 2);
                        }

                        var rangeWithData = cell.InsertData(data.Tables[businFlag]);
                        cell = cell.CellBelow(rangeWithData.RowCount());
                    }
                    else
                    {
                        cell = cell.CellBelow();
                    }
                }
            }
        }
    }
}